﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DBAccess
/// </summary>
public class DBAccess
{
    private static DataSet ds;
    private static string sqlString;
    private static SqlConnection con = new SqlConnection();
    public static int TaoTaiKhoan(string TenDangNhap ,string MatKhau ,string Ten ,int NoiSinhID ,string  Mobile, string Phone, string Email, string Website,string  CMND,DateTime NgaySinh,int  QuyenHan,string  Avarta,int  NganHangID ,string  SoTK )
    {
        int result = 0;
        con = GetCenection.GetConection();
        sqlString = "TaoTaiKhoan";
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
           con.Open();
           command.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar,50).Value = TenDangNhap ;
	       command.Parameters.Add("@MatKhau", SqlDbType.NVarChar,100).Value = MatKhau ;
	       command.Parameters.Add("@Ten", SqlDbType.NVarChar,150).Value =  Ten;
	       command.Parameters.Add("@NoiSinhID", SqlDbType.Int).Value = NoiSinhID ;
	       command.Parameters.Add("@Mobile", SqlDbType.NVarChar,100).Value = Mobile ;
	       command.Parameters.Add("@Phone", SqlDbType.NVarChar,100).Value = Phone ;
	       command.Parameters.Add("@Email", SqlDbType.NVarChar,100).Value = Email ;
	       command.Parameters.Add("@Website", SqlDbType.NVarChar,100).Value = Website  ;
	       command.Parameters.Add("@CMND", SqlDbType.NVarChar,20).Value =CMND  ;
	       command.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value =NgaySinh ;
	       command.Parameters.Add("@QuyenHan", SqlDbType.Int).Value = 1;// đã kích hoặt
	       command.Parameters.Add("@Avarta", SqlDbType.NVarChar,250).Value =  Avarta;
	       command.Parameters.Add("@NganHangID", SqlDbType.Int).Value = NganHangID;
	       command.Parameters.Add("@SoTK", SqlDbType.NVarChar,20).Value =  SoTK;
           command.Parameters.Add("@DangNhapTruoc", SqlDbType.DateTime).Value =DateTime.Now ;
           result = (int)command.ExecuteNonQuery();
           con.Close();
        }
        catch
        {
            result = 0;
            con.Close();
        }
        return result;
    }  
    public static int ktTaiKhoan(string idAcount, string pass)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string sqlQuery = "";
        if (pass.Equals(""))
            sqlQuery = "select QuyenHan from TaiKhoan where TaiKhoan.TenDangNhap = '" + idAcount + "'";
        else
            sqlQuery = "select QuyenHan from TaiKhoan where TaiKhoan.TenDangNhap = '" + idAcount + "' AND TaiKhoan.MatKhau ='" + pass + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            try
            {
                result = (int)command.ExecuteScalar();
            }
            catch(NullReferenceException) {
                result = -1;
            }
        }
        catch (SqlException)
        {
            return 0;
        }
        return result;
    }  
    public static TaiKhoan layTK(string tenDangNhap, string matKhau)
    {
        ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "select * from TaiKhoan where TaiKhoan.TenDangNhap = '" + tenDangNhap + "' AND TaiKhoan.MatKhau ='" + matKhau + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "Acount");
            con.Close();
            TaiKhoan acount = new TaiKhoan();
            acount.Avarta = ds.Tables[0].Rows[0]["Avarta"].ToString();
            acount.Ten = ds.Tables[0].Rows[0]["Ten"].ToString();
            acount.TenDangNhap = ds.Tables[0].Rows[0]["TenDangNhap"].ToString();
            acount.QuyenHan = ds.Tables[0].Rows[0]["QuyenHan"].ToString();
            acount.NoiSinhID = (int)ds.Tables[0].Rows[0]["NoiSinhID"];
            acount.Email = ds.Tables[0].Rows[0]["Email"].ToString();
            acount.Website = ds.Tables[0].Rows[0]["Website"].ToString();
            acount.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString();
            acount.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
            acount.CMND = ds.Tables[0].Rows[0]["CMND"].ToString();
            acount.NgaySinh = (DateTime)ds.Tables[0].Rows[0]["NgaySinh"];
            acount.NganHangID = (int)ds.Tables[0].Rows[0]["NganHangID"];
            acount.SoTK = ds.Tables[0].Rows[0]["SoTK"].ToString();
            acount.DangNhapTruoc = (DateTime)ds.Tables[0].Rows[0]["DangNhapTruoc"];
            return acount;
        }
        catch
        {
            con.Close();
            return null;
        }
    }

    //public static int UpdateAcount(Acount acount)
    //{

    //    con = GetCenection.GetConection();
    //    string sqlQuery = "UPDATE Acount SET " +
    //                      "Pass ='" + acount.Pass + "'" +
    //                      ",Name ='" + acount.Name + "'" +
    //                      ",Email ='" + acount.Email + "'" +
    //                      ",Address ='" + acount.Address + "'" +
    //                      ",BankName ='" + acount.BankName + "'" +
    //                      ",IDBank ='" + acount.BankID + "'" +
    //                      ",Point ='" + acount.Point + "'" +
    //                      ",BirthDay ='" + acount.BirthDay.ToShortDateString() + "'" +
    //                          ",Avarta ='" + acount.Avarta + "'" +
    //                          ",CMND ='" + acount.CMND + "'" +
    //                          ",Mobile ='" + acount.Mobile + "'" +
    //                        "WHERE Acount.IDAcount ='" + acount.IDAcount + "'";
    //    SqlCommand command = new SqlCommand(sqlQuery, con);
    //    command.CommandType = CommandType.Text;
    //    con.Open();
    //    try
    //    {
    //        command.ExecuteNonQuery();
    //        con.Close();
    //    }
    //    catch
    //    {
    //        con.Close();
    //    }
    //}
    public static int GuiThu(Thu thu)
    {
        con = GetCenection.GetConection();
        sqlString = "GuiThu";
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
            con.Open();
            command.Parameters.Add("@NgayGui", SqlDbType.DateTime).Value = thu.NgayGui;
            command.Parameters.Add("@NguoiGui", SqlDbType.NVarChar, 50).Value = thu.NguoiGui;
            command.Parameters.Add("@NguoiNhan", SqlDbType.NVarChar, 50).Value = thu.NguoiNhan;
            command.Parameters.Add("@TieuDe", SqlDbType.NVarChar, 150).Value = thu.TieuDe;
            command.Parameters.Add("@NoiDung", SqlDbType.NVarChar, 500).Value = thu.NoiDung;
            command.Parameters.Add("@OpenID", SqlDbType.Bit).Value = thu.OpenID;
            command.Parameters.Add("@Email", SqlDbType.NVarChar, 150).Value = thu.Email;
            command.ExecuteNonQuery();
            con.Close();
            return 1;
        }
        catch
        {
            con.Close();
            return 0;
        }
    }

    internal static int getGianHang(string acount)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string sqlQuery = "select count(TenDangNhap) from GianHang where GianHang.TenDangNhap = '" + acount + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            result = (int)command.ExecuteScalar();
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return 0;
        }
        return result;
    }

    internal static int updateAcount(TaiKhoan acount)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string sqlQuery = "update TaiKhoan set " +
            "Ten = '" + acount.Ten +"'"+
            ",NoiSinhID ='" + acount.NoiSinhID + "'" +
            ",Mobile ='" + acount.Mobile + "'" +
            ",Phone ='" + acount.Phone + "'" +
            ",Email ='" + acount.Email + "'" +
            ",Website ='" + acount.Website + "'" +
            ",CMND ='" + acount.CMND + "'" +
            ",NgaySinh ='" + acount.NgaySinh + "'" +
            ",QuyenHan ='" + acount.QuyenHan + "'" +
            ",Avarta ='" + acount.Avarta + "'" +
            ",NganHangID ='" + acount.NganHangID + "'" +
            ",SoTK ='" + acount.SoTK + "'" +
            " Where TaiKhoan.TenDangNhap='" + acount.TenDangNhap+"'";
      
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            result = command.ExecuteNonQuery();
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return 0;
        }
        return result;
    }

    internal static Thu[] layThu(string tendangnhap, int number, int minIDHomTHu, bool isInbox)
    {
        ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "select Top " + number + " * from HomThu where "+
            (isInbox == true ? "NguoiNhan = '" : "NguoiGui = '") + tendangnhap + "'"+            
            (minIDHomTHu>0?(" AND IDHomThu <" + minIDHomTHu):"") +"  order by NgayGui DESC";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "HomThu");
            con.Close();
            Thu[] mails = new Thu[ds.Tables[0].Rows.Count];
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                mails[i] = new Thu();
                mails[i].IDHomThu = (int)ds.Tables[0].Rows[i]["IDHomThu"];
                mails[i].NguoiGui = ds.Tables[0].Rows[i]["NguoiGui"].ToString();
                mails[i].NguoiNhan = ds.Tables[0].Rows[i]["NguoiNhan"].ToString();
                mails[i].TieuDe = ds.Tables[0].Rows[i]["TieuDe"].ToString();
                mails[i].NoiDung = ds.Tables[0].Rows[i]["NoiDung"].ToString();
                mails[i].NgayGui = (DateTime)ds.Tables[0].Rows[i]["NgayGui"];
                mails[i].DaDoc = (bool)ds.Tables[0].Rows[i]["DaDoc"];
            }
            return mails;
        }
        catch
        {
            con.Close();
            return null;
        }
    }

    internal static int[] laySoluongThu(string name, bool isInbox)
    {
        int[] result ={0,0};
        ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "select Dadoc,count( "+(isInbox?"NguoiNhan":"NguoiGui")+" )as Number  From HomThu where  "+
            (isInbox?"NguoiNhan":"NguoiGui")+"  ='"+name+"' group by dadoc ";
        
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            con.Close();
            result[0] = (int)ds.Tables[0].Rows[0]["Number"];
            result[1] = (int)ds.Tables[0].Rows[1]["Number"];
            return result;
        }
        catch
        {
            con.Close();
            return null;
        }
    }
    internal static List<GianHang> getListGianHangAcount(string tenDangNhap)
    {
        ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "select * from View_GianHang where View_GianHang.TenDangNhap ='" + tenDangNhap + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "GianHang");
            con.Close();
            List<GianHang> gianHangs = new List<GianHang>();
            for (int i = 0; i < ds.Tables["GianHang"].Rows.Count; i++)
            {
                GianHang gh = new GianHang();
                gh.IDGianHang = (int)ds.Tables[0].Rows[i]["IDGianHang"];
                gh.TenGianHang = ds.Tables[0].Rows[i]["TenGianHang"].ToString();
                gh.Thich = (int)ds.Tables[0].Rows[i]["Thich"];
                gh.KhongThich = (int)ds.Tables[0].Rows[i]["KhongThich"];
                gh.LuotXem = (int)ds.Tables[0].Rows[i]["LuotXem"];
                gh.DiaChi = ds.Tables[0].Rows[i]["DiaChi"].ToString();
                gh.LinkLogo = ds.Tables[0].Rows[i]["LinkLogo"].ToString();
                gh.NgayTao = (DateTime)ds.Tables[0].Rows[i]["NgayTao"];
                gh.Phone = ds.Tables[0].Rows[i]["Phone"].ToString();
                gh.Slogan = ds.Tables[0].Rows[i]["Slogan"].ToString();
                gh.ThongTin = ds.Tables[0].Rows[i]["ThongTin"].ToString();
                gh.XacThuc = (bool)ds.Tables[0].Rows[i]["XacThuc"];
                gh.TenNganHang = ds.Tables[0].Rows[i]["TenDayDu"].ToString();
                gh.SoTaiKhoan = ds.Tables[0].Rows[i]["SoTaiKhoan"].ToString(); ;
                gh.ChuTaiKhoan = ds.Tables[0].Rows[i]["ChuTaiKhoan"].ToString(); ;
                gh.Email = ds.Tables[0].Rows[i]["Email"].ToString(); ;
                gh.Banner = ds.Tables[0].Rows[i]["Banner"].ToString(); ;
                gh.LinkBackground = ds.Tables[0].Rows[i]["LinkBackground"].ToString(); 
                gh.KhuVucBan = ds.Tables[0].Rows[i]["Ten"].ToString();
                gh.TagTimKiem = ds.Tables[0].Rows[i]["TagTimKiem"].ToString(); 
                gh.Lat = (int)ds.Tables[0].Rows[i]["Lat"];
                gh.Lon = (int)ds.Tables[0].Rows[i]["Long"];
                gh.KhuVucBanInt = (int)ds.Tables[0].Rows[i]["KhuVucBan"];
                gh.NganHangInt = (int)ds.Tables[0].Rows[i]["NganHangID"];
                gh.Sanphams = getGianHangSanPhamFromGianHangID(gh.IDGianHang);
                gianHangs.Add(gh);                
            }
            return gianHangs;
        }
        catch
        {
            con.Close();
            return null;
        }
    }
    internal static List<GianHangSanPham> getGianHangSanPhamFromGianHangID(int gianHangID)
    {
        DataSet ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "select * from GianHangSanPham where GianHangSanPham.GianHangID ='" + gianHangID + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "GianHangSanPham");
            con.Close();
            List<GianHangSanPham> gianHangSanPhams = new List<GianHangSanPham>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                SanPham sp = getSanPham((int)ds.Tables[0].Rows[i]["SanPhamID"]);
                GianHangSanPham ghsp = new GianHangSanPham(sp);
                ghsp.IDGianHang = (int)ds.Tables[0].Rows[i]["GianHangID"];
                ghsp.SoLuong = (int)ds.Tables[0].Rows[i]["SoLuong"];
                ghsp.Gia = (Decimal)ds.Tables[0].Rows[i]["Gia"];
                ghsp.MaSo = ds.Tables[0].Rows[i]["Maso"].ToString();
                ghsp.Anh = ds.Tables[0].Rows[i]["Anh"].ToString();
                ghsp.ThongTinKhac = ds.Tables[0].Rows[i]["ThongTinKhac"].ToString();

                
                gianHangSanPhams.Add(ghsp);
            }
            return gianHangSanPhams;
        }
        catch
        {
            con.Close();
            return null;
        }
    }
    internal static SanPham getSanPham(int sanPhamID)
    {
        DataSet ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "select * from SanPham where SanPham.IDSanPham ='" + sanPhamID + "' select * from ThongSoKiThuat where ThongSoKiThuat.SanPhamID ='" + sanPhamID + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        SanPham sp = new SanPham(); 
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            con.Close();
            List<ThongSoKiThuat> thongSos = new List<ThongSoKiThuat>();
            for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
            {
                ThongSoKiThuat tskt = new ThongSoKiThuat();
                tskt.Ten = ds.Tables[1].Rows[i]["Ten"].ToString();
                tskt.NoiDung = ds.Tables[1].Rows[i]["NoiDung"].ToString();
                thongSos.Add(tskt);
            }
            sp = new SanPham((int)ds.Tables[0].Rows[0]["DanhMucID"],(int)ds.Tables[0].Rows[0]["IDSanPham"]);
            sp.AnhSPGoc = ds.Tables[0].Rows[0]["AnhSPGoc"].ToString();
            sp.TenSanPham = ds.Tables[0].Rows[0]["TenSanPham"].ToString();
            sp.ThongSos = thongSos;
            return sp;
        }
        catch
        {
            con.Close();
            return sp;
        }
    }

    internal static List<YKien> geListYKien(int idObject,int tableName,int minID )
    {//0 YKien San Pham 1 Y kien Gian Hang
        ds = new DataSet();
        con = GetCenection.GetConection();
        string table = (tableName == 0 ? "View_YKienSanPham" : "View_YKienGianHang");
        string column = (tableName == 0 ? "View_YKienSanPham.SanPhamID" : "View_YKienGianHang.GianHangID");
        string sqlQuery = "select * from " + table + " where " +column + " =" + idObject+
        (minID > 0 ? (" AND " + column + " <" + minID) : "") + "  order by " + table + ".NgayLap DESC";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "YKienSanPham");
            con.Close();
            List<YKien> ykiens = new List<YKien>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                YKien yk = new YKien();
                yk.IDYKien = (int)ds.Tables[0].Rows[i]["IDYKien"];
                yk.ObjectID = (int)ds.Tables[0].Rows[i][(tableName == 0 ? "SanPhamID" : "GianHangID")];
                yk.OpenID =  Boolean.Parse(ds.Tables[0].Rows[i]["OpenID"].ToString());
                yk.Vote = (int)ds.Tables[0].Rows[i]["Vote"];

                yk.Ten = ds.Tables[0].Rows[i]["Ten"].ToString();
                yk.TenDangNhap = ds.Tables[0].Rows[i]["TenDangNhap"].ToString();
                yk.NoiDung = ds.Tables[0].Rows[i]["NoiDung"].ToString();
                yk.Avarta = ds.Tables[0].Rows[i]["Avarta"].ToString();
                yk.NgayLap = (DateTime)ds.Tables[0].Rows[i]["NgayLap"];
                ykiens.Add(yk);
            }
            return ykiens;
        }
        catch
        {
            con.Close();
            return null;
        }
    }

    internal static int voteYkien(int idObject, int numTable)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string table = (numTable == 0 ? "YKienSanPham" : "YKien");
        string column = (numTable == 0 ? "YKienSanPham.IDYKien" : "YKien.IDYKien");
        string sqlQuery = "UPDATE " + table + " SET Vote = Vote+1 WHERE " + column + " =" + idObject;
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            result = command.ExecuteNonQuery();
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return 0;
        }
        return result;
    }

    internal static int addYkien(int idObject, string noiDung, string tenDangNhap,string email,int numTable )
    {
        int result = 0;
        con = GetCenection.GetConection();
        sqlString = (numTable == 0 ? "ThemYKienSanPham" : "ThemYKienGianHang");
        SqlCommand command = new SqlCommand(sqlString, con);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
            con.Open();
            command.Parameters.Add("@ObjectID", SqlDbType.Int).Value = idObject;
            command.Parameters.Add("@OpenID", SqlDbType.Bit).Value = (tenDangNhap.Equals("")?true:false);
            command.Parameters.Add("@Email", SqlDbType.NVarChar, 150).Value = email;
            command.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar, 50).Value = tenDangNhap;
            command.Parameters.Add("@NoiDung", SqlDbType.NVarChar, 100).Value = noiDung;          
            command.Parameters.Add("@NgayLap", SqlDbType.DateTime).Value = DateTime.Now;
            result = (int)command.ExecuteNonQuery();
            con.Close();
        }
        catch
        {
            result = 0;
            con.Close();
        }
        return result;
        /////       
    }

    internal static int createOrther(string tenDangNhap, string[] listGianHangIDs, string[] listSanPhamIDs, string[] slitMasoSPs,int[] soLuong)
    {
        int result = 0;
        con = GetCenection.GetConection();
        sqlString = "TaoOrder";
        SqlCommand command1 = new SqlCommand(sqlString, con);
        command1.CommandType = CommandType.StoredProcedure;
        try
        {      
            con.Open();
            command1.Parameters.Add("@TenDangNhap", SqlDbType.NVarChar, 50).Value = tenDangNhap;
            command1.Parameters.Add("@MaXN", SqlDbType.NVarChar, 15).Value = MyFunction.stringRandom(6);
            command1.Parameters.Add("@NgayLap", SqlDbType.DateTime).Value = DateTime.Now;
            for (int i = 0; i < soLuong.Length; i++)
            {
                SqlCommand command = command1;
                command.Parameters.Add("@GianHangID", SqlDbType.Int).Value = listGianHangIDs[i];
                command.Parameters.Add("@SanPhamID", SqlDbType.Int).Value = listSanPhamIDs[i];
                command.Parameters.Add("@SoLuong", SqlDbType.Int).Value = soLuong[i];
                command.Parameters.Add("@MasoSP", SqlDbType.NVarChar, 20).Value = slitMasoSPs[i];
                result += (int)command.ExecuteNonQuery();
            }
            con.Close();
        }
        catch
        {
            result = 0;
            con.Close();
        }
        return result;
    }

    internal static List<GianHang> searchSanPham(int typeSearch, string strSearch)
    {
        ds = new DataSet();
        con = GetCenection.GetConection();
        string sqlQuery = "";
        switch (typeSearch)
        {
            case 0:
                sqlQuery = "Select * from View_GianHang where IDGIanHang IN("
                + "Select distinct GianHangID from GianHangSanPham where GianHangSanPham.SanPhamID IN ("
                + "select IDSanPham from View_SanPham where Lower(LTRIM(RTRIM(dbo.fuChuyenCoDauThanhKhongDau(View_SanPham.TenSanPham))))LIKE '%" + MyFunction.LocDau(strSearch).ToLower() + "%' ))";
                break;
            case 1:
                int[] idDanhMuc = getIDDanhMuc(strSearch);
                DataTable tbDanhMuc = loadDanhMuc();
                for (int i = 0; i < idDanhMuc.Length; i++)
                {
                    string strChild = "," + idDanhMuc[i] + findChildInDanhMuc(idDanhMuc[i], tbDanhMuc);
                    string strListIDGianHang = getIDGianHangOfDanhMucID(strChild.Substring(1));
                    sqlQuery += " Select distinct * from View_GianHang where IDGIanHang IN(" + ((strListIDGianHang.Length > 0) ? strListIDGianHang.Substring(1) : strListIDGianHang) + ");";                  
                }
                break;
            case 2:
                sqlQuery = "Select * from View_GianHang where Lower(LTRIM(RTRIM(dbo.fuChuyenCoDauThanhKhongDau(View_GianHang.TenGianHang))))LIKE '%" + MyFunction.LocDau(strSearch).ToLower() + "%' ";
                break;
            case 3:
                sqlQuery = "Select * from View_GianHang where View_GianHang.TenDangNhap LIKE '%" + MyFunction.LocDau(strSearch).ToLower() + "%' ";
                break;

        }        
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "GianHang");
            con.Close();
            List<GianHang> gianHangs = new List<GianHang>();
            for (int i = 0; i < ds.Tables["GianHang"].Rows.Count; i++)
            {
                GianHang gh = new GianHang();
                gh.IDGianHang = (int)ds.Tables[0].Rows[i]["IDGianHang"];
                gh.TenGianHang = ds.Tables[0].Rows[i]["TenGianHang"].ToString();
                gh.Thich = (int)ds.Tables[0].Rows[i]["Thich"];
                gh.KhongThich = (int)ds.Tables[0].Rows[i]["KhongThich"];
                gh.LuotXem = (int)ds.Tables[0].Rows[i]["LuotXem"];
                gh.DiaChi = ds.Tables[0].Rows[i]["DiaChi"].ToString();
                gh.LinkLogo = ds.Tables[0].Rows[i]["LinkLogo"].ToString();
                gh.NgayTao = (DateTime)ds.Tables[0].Rows[i]["NgayTao"];
                gh.Phone = ds.Tables[0].Rows[i]["Phone"].ToString();
                gh.Slogan = ds.Tables[0].Rows[i]["Slogan"].ToString();
                gh.ThongTin = ds.Tables[0].Rows[i]["ThongTin"].ToString();
                gh.XacThuc = (bool)ds.Tables[0].Rows[i]["XacThuc"];
                gh.TenNganHang = ds.Tables[0].Rows[i]["TenDayDu"].ToString();
                gh.SoTaiKhoan = ds.Tables[0].Rows[i]["SoTaiKhoan"].ToString(); ;
                gh.ChuTaiKhoan = ds.Tables[0].Rows[i]["ChuTaiKhoan"].ToString(); ;
                gh.Email = ds.Tables[0].Rows[i]["Email"].ToString(); ;
                gh.Banner = ds.Tables[0].Rows[i]["Banner"].ToString(); ;
                gh.LinkBackground = ds.Tables[0].Rows[i]["LinkBackground"].ToString();
                gh.KhuVucBan = ds.Tables[0].Rows[i]["Ten"].ToString();
                gh.Lat = (int)ds.Tables[0].Rows[i]["Lat"];
                gh.Lon = (int)ds.Tables[0].Rows[i]["Long"];
                gh.Sanphams = getGianHangSanPhamFromGianHangID(gh.IDGianHang);
                gianHangs.Add(gh);
            }
            return gianHangs;
        }
        catch
        {
            con.Close();
            return null;
        }
    }

    internal static int[] getIDDanhMuc(string strSearch)
    {
        DataSet ds = new DataSet();
        int[] result;
        con = GetCenection.GetConection();
        string sqlQuery = "Select IDDanhMuc from DanhMuc where Lower(LTRIM(RTRIM(dbo.fuChuyenCoDauThanhKhongDau(DanhMuc.TenNhanBiet))))LIKE '%" + MyFunction.LocDau(strSearch).ToLower() + "%'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
           
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "DanhMuc");
            con.Close();
            result = new int[ds.Tables["DanhMuc"].Rows.Count];

            for (int i = 0; i < ds.Tables["DanhMuc"].Rows.Count; i++)
            {
                result[i] = (int)ds.Tables["DanhMuc"].Rows[i][0];
            }
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return null;
        }
        return result;
    }

    internal static DataTable loadDanhMuc()
    {
            DataSet ds= new DataSet();
            con = GetCenection.GetConection();
            string sqlQuery = "select * from DanhMuc ";
            SqlCommand command = new SqlCommand(sqlQuery, con);
            command.CommandType = CommandType.Text;
            con.Open();
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "DanhMuc");
                con.Close();
                return ds.Tables["DanhMuc"];
            }
            catch
            {
                con.Close();
                return null;
            }
    }

    internal static  string findChildInDanhMuc(int parent, DataTable table)
    {
 	    bool haveChild = false;
        string result = "";
        foreach (DataRow row1 in table.Rows)
        {
            if (int.Parse(row1["ParentID"].ToString()) == parent )
            {
                haveChild = true;
                result+= ","+row1["IDDanhMuc"];
                result += findChildInDanhMuc(int.Parse(row1["IDDanhMuc"].ToString()), table);
            }
        }
        if (!haveChild)
            return "";
        return result;
    }

    internal static string getIDGianHangOfDanhMucID(string listDanhMuc)
    {
        DataSet ds = new DataSet();
        string result="";
        con = GetCenection.GetConection();
        string sqlQuery = "Select distinct View_SanPham.GianHangID from View_SanPham where DanhMucID In (" + listDanhMuc + "); ";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "GianHangID");
            con.Close();
            for (int i = 0; i < ds.Tables["GianHangID"].Rows.Count; i++)
            {
                result+=","+ds.Tables["GianHangID"].Rows[i][0].ToString();
            }
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return "";
        }
        return result.Length>0?result.Substring(1):result;
    }

    internal static List<SanPham> getListSanPhamOfDanhMuc(int iDDanhMuc)
    {
        DataSet ds = new DataSet();      
        con = GetCenection.GetConection();
        string sqlQuery = "Select *  from SanPham where SanPham.DanhMucID =" + iDDanhMuc + ";";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "SanPham");
            con.Close();
            List<SanPham> results = new List<SanPham>();
            for (int i = 0; i < ds.Tables["SanPham"].Rows.Count; i++)
            {
                SanPham sp = new SanPham();
                sp.AnhSPGoc = ds.Tables[0].Rows[i]["AnhSPGoc"].ToString();
                sp.DanhMucID = iDDanhMuc;
                sp.TenSanPham = ds.Tables[0].Rows[i]["TenSanPham"].ToString();
                sp.IDSanPham = (int)ds.Tables[0].Rows[i]["IDSanPham"];
                sp.ThongSos = getListThongSos(sp.IDSanPham);
                results.Add(sp);
            }
            return results;
        }
        catch (SqlException)
        {
            con.Close();
            return null;
        }
        return null;
    }
    
    private static List<ThongSoKiThuat> getListThongSos(int sanPhamID)
    {
        DataSet ds = new DataSet();
        string result = "";
        con = GetCenection.GetConection();
        string sqlQuery = "select * from ThongSoKiThuat where ThongSoKiThuat.SanPhamID ='" + sanPhamID + "'";
        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds, "ThongSo");
            con.Close();
            List<ThongSoKiThuat> thongSos = new List<ThongSoKiThuat>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                ThongSoKiThuat tskt = new ThongSoKiThuat();
                tskt.Ten = ds.Tables[0].Rows[i]["Ten"].ToString();
                tskt.NoiDung = ds.Tables[0].Rows[i]["NoiDung"].ToString();
                thongSos.Add(tskt);
            }
            return thongSos;
        }
        catch (SqlException)
        {
            con.Close();
            return null;
        }
    }

    internal static int changePass(string tendangnhap, string matkhaucu, string matkhaumoi)
    {
        int result = 0;
        con = GetCenection.GetConection();
        string sqlQuery = "update TaiKhoan set TaiKhoan.MatKhau ='" + MyFunction.MahoaMatKhau(matkhaumoi) + "' WHERE TaiKhoan.MatKhau ='" + MyFunction.MahoaMatKhau(matkhaucu) + "' AND TaiKHoan.TenDangNhap ='" + tendangnhap + "';";

        SqlCommand command = new SqlCommand(sqlQuery, con);
        command.CommandType = CommandType.Text;
        con.Open();
        try
        {
            result = command.ExecuteNonQuery();
            con.Close();
            return result;
        }
        catch (SqlException)
        {
            con.Close();
            return 0;
        }
        return result;
    }
}